/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import Classes.Materiel;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

/**
 *
 * @author Valentin
 */
public class MaterielDAO extends DAO<Materiel> {
    
    public MaterielDAO(Connection conn) {
        super(conn);
    }
    
    /**
     * La fonction prend en paramètre un objet Materiel et insère les informations de l'objet en base de données.
     * @param matos
     * @throws SQLException 
     */
    @Override
    public void insert(Materiel matos) throws SQLException {
        String query = " INSERT INTO"
                + " materiel"
                + " ( numCompta, numSerie, nom, descriptif, dateHT, prixHT, garantie, numBC, budget, amortissement, idT, idF, idM, idS, idSc)"
                + " VALUES ("
                + matos.getNumCompta() + ","
                + "\"" + matos.getNumSerie() + "\","
                + "\"" + matos.getNom() + "\","
                + "\"" + matos.getDescriptif() + "\","
                + "\"" + matos.getDateHT() + "\","
                + matos.getPrixHT() + ","
                + matos.getGarantie() + ","
                + "\"" + matos.getNumBC() + "\","
                + "\"" + matos.getBudget() + "\","
                + matos.getAmortissement() + ","
                + matos.getIdT() + ","
                + matos.getIdF() + ","
                + matos.getIdM() + ","
                + matos.getIdS() + ","
                + matos.getIdSc() + ")";
        
        state.executeUpdate(query, state.RETURN_GENERATED_KEYS);
        
        try (ResultSet rs = state.getGeneratedKeys()) {
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            if (rs.next()) {
                do {
                    for (int i = 1; i <= columnCount; i++) {
                        int key = rs.getInt(i);
                        matos.setId(key);
                    }
                } while (rs.next());
            } else {
                matos.setId(-1);
            }
            
            matos.createCodeBarre();
            
            query = " UPDATE  materiel "
                    + " SET numCB = " + matos.getNumCB()
                    + " WHERE id = " + matos.getId();
            System.out.println(query);
            state.executeUpdate(query);
            
        }
    }
    
    /**
     * La fonction prend en paramètre un objet Materiel et supprime l'entrée correspondante à l'objet en base de données.
     * @param matos
     * @throws SQLException 
     */
    @Override
    public void delete(Materiel matos) throws SQLException {
        String query = " DELETE FROM"
                + " materiel"
                + " WHERE numCB ="
                + matos.getNumCB();
        
        state.executeUpdate(query);
    }
    
    /**
     * La fonction prend en paramètre un objet Materiel et met à jour les informations de l'entré correspondante à l'id de l'objet.
     * @param matos
     * @throws SQLException 
     */
    @Override
    public void update(Materiel matos) throws SQLException {
        String query = " UPDATE  materiel"
                + " SET"
                + "  numCompta = " + matos.getNumCompta()
                + " ,numSerie = \"" + matos.getNumSerie() + "\""
                + " ,nom = \"" + matos.getNom() + "\""
                + " ,descriptif = \"" + matos.getDescriptif() + "\""
                + " ,dateHT = \"" + matos.getDateHT() + "\""
                + " ,prixHT = " + matos.getPrixHT()
                + " ,garantie = " + matos.getGarantie()
                + " ,numBC = \"" + matos.getNumBC() + "\""
                + " ,budget = \"" + matos.getBudget() + "\""
                + " ,amortissement = " + matos.getAmortissement()
                + " ,idT = " + matos.getIdT()
                + " ,idF = " + matos.getIdF()
                + " ,idM = " + matos.getIdM()
                + " ,idS = " + matos.getIdS()
                + " ,idSc = " + matos.getIdSc()
                + " WHERE numCB = " + matos.getNumCB();
        state.executeUpdate(query);
    }
    
    /**
     * La fonction recherche tous les matériels de la base de données et construit une Map d'objet matériel
     * @return 
     */
    public Map<Long, Materiel> findAll() {
        Map<Long, Materiel> listeMateriel = new HashMap();
        ResultSet result2;
        try {
            String query = "SELECT * FROM materiel";
            result2 = state.executeQuery(query);
            while (result2.next()) {
                listeMateriel.put(result2.getLong("numCB"), new Materiel(result2.getLong("numCB"), result2.getInt("numCompta"), result2.getString("numSerie"), result2.getString("nom"), result2.getString("descriptif"), result2.getString("dateHT"), result2.getDouble("prixHT"), result2.getInt("garantie"), result2.getString("numBC"), result2.getString("budget"), result2.getDouble("amortissement"), result2.getInt("idT"), result2.getInt("idM"), result2.getInt("idS"), result2.getInt("idSc")));
            }
            return listeMateriel;
            
            
        } catch (SQLException exc) {
            System.out.println("[Debug] Problème query : MaterielDAO - findAll()");
            exc.printStackTrace();
            
        }
        return null;
    }
    
   /**
    * @author Valentin
    * @param L'id d'une salle
    * @return une map (IDSalle , Materiel) contenant tout les matériel d'une salle
    */
    public Map<Long, Materiel> loadBySalle(int IDSalle) {
        Map<Long, Materiel> listeMateriel = new HashMap();
        ResultSet result;
        
        try {
            String query = "SELECT * FROM materiel"
                    + " WHERE idS = " + IDSalle;
            result = state.executeQuery(query);
            while (result.next()) {
                listeMateriel.put(result.getLong("numCB"), new Materiel(result.getLong("numCB"), result.getInt("numCompta"), result.getString("numSerie"), result.getString("nom"), result.getString("descriptif"), result.getString("dateHT"), result.getDouble("prixHT"), result.getInt("garantie"), result.getString("numBC"), result.getString("budget"), result.getDouble("amortissement"), result.getInt("idT"), result.getInt("idM"), result.getInt("idS"), result.getInt("idSc")));
            }
            
            
            
        } catch (SQLException exc) {
            System.out.println("[Debug] Problème query : MaterielDAO - loadBySalle()");
            exc.printStackTrace();
            
        }
        return listeMateriel;
    }
    /**
     * @author Valentin
     * @param le numéro de code barre
     * @return l'instance de matériel correspondant 
     */
    public Materiel loadByCodeBarre(Long CB) {
        Materiel myMateriel = new Materiel();
        Map<Long, Materiel> listeMateriel = new HashMap();
        ResultSet result;
        
        try {
            String query = "SELECT * FROM materiel"
                    + " WHERE numCB = " + CB;
            result = state.executeQuery(query);
            while (result.next()) {
                
                myMateriel.setNumCB(result.getLong("numCB"));
                myMateriel.setNumCompta(result.getInt("numCompta"));
                myMateriel.setNumSerie(result.getString("numSerie"));
                myMateriel.setNom(result.getString("nom"));
                myMateriel.setDescriptif(result.getString("descriptif"));
                myMateriel.setDateHT(result.getString("dateHT"));
                myMateriel.setPrixHT(result.getDouble("prixHT"));
                myMateriel.setGarantie(result.getInt("garantie"));
                myMateriel.setNumBC(result.getString("numBC"));
                myMateriel.setBudget(result.getString("budget"));
                myMateriel.setAmortissement(result.getDouble("amortissement"));
                myMateriel.setIdT(result.getInt("idT"));
                myMateriel.setIdM(result.getInt("idM"));
                myMateriel.setIdS(result.getInt("idS"));
                myMateriel.setIdSc(result.getInt("idSc"));
                                
            }
            
            
        } catch (SQLException exc) {
            System.out.println("[Debug] Problème query : MaterielDAO - loadBySalle()");
            exc.printStackTrace();
            
        }
        return myMateriel;
    }
}
